/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pos.system.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import pos.system.OrderItem;
import pos.system.Role;
import pos.system.StockItem;

/**
 *
 * @author shameera
 */
public class MySqlConnector {

    private Connection connection;
    private Statement statement;
    private String databaseUrl = "jdbc:mysql://localhost:3306/"+DBConstant.LEAFPOS_DATABASE;
    private ResultSet stockData;
//    private String database = "`pos_db`";
//    private String credential = "`credential`";

    public MySqlConnector(String username, String password) throws SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(databaseUrl, username, password);
            statement = connection.createStatement();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MySqlConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void createConnection(String username, String password) throws SQLException {
        try {
            if (!isClosed()) {
                close();
            }
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(databaseUrl, username, password);
            statement = connection.createStatement();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MySqlConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void close() throws SQLException {
        statement.close();
        connection.close();
    }

    public boolean isClosed() throws SQLException {
        return connection.isClosed();
    }
    
    public int insert(StockItem item) throws SQLException{
        String query = "INSERT INTO " + DBConstant.DATABASE + ".`" + DBConstant.STOCK_TABLE + "` ("
                + generateColumnsNames(new String[] {"CODE", "NAME", "SELLER", "QTY", "UNIT_PRICE", "DATE"}) +
                ") VALUES (" + generateValuesWithDate(new String[] {item.getCode(), item.getName() , item.getSeller(),
                item.getQtyAsString() , item.getUnit_priceAsMoneyString()}) + ");";
        return insert(query);
    }

    public int insert(String invoice , int item_num , OrderItem item ) throws SQLException{
                 String query = "INSERT INTO " + DBConstant.DATABASE + ".`" + DBConstant.ORDER_TABLE + "` ("
                + generateColumnsNames(new String[] {"INVOICE", "ITEM_ID", "CODE", "ITEM_NAME", "QTY","SELLER","UNIT_PRICE"}) +
                ") VALUES (" + generateValuesWithOutDate(new String[] {invoice , item_num+"" , item.getCode(), item.getName() ,item.getAmount()+"", item.getSeller(),
                item.getPriceAsMoneyString()}) + ");";
        return insert(query);
    }
    public int insert(String table, String[] columnNames, String[] data) throws SQLException {
        String query = "INSERT INTO " + DBConstant.DATABASE + ".`" + table + "` ("
                + generateColumnsNames(columnNames) + ") VALUES (" + generateValuesWithDate(data) + ");";
        return insert(query);
    }

    public int insert(String query) throws SQLException {
        int ret = statement.executeUpdate(query);
        return ret;
    }
    
    public int delete(String query) throws SQLException{
        return statement.executeUpdate(query);
    }
    
    public int update(String query) throws SQLException{
        return statement.executeUpdate(query);
    }

    public ResultSet retrieveAllvalues(String table) throws SQLException {
                String query ;
        if(table.equals(DBConstant.CREDENTIAL_TABLE_DB_STRING)){
            query = "SELECT " + DBConstant.CREDENTIAL_USERNAME + ", "+
                        DBConstant.CREDENTIAL_ROLE +", " +
                        DBConstant.CREDENTIAL_DATE +" " + 
                        "FROM "
                + DBConstant.DATABASE + "." + DBConstant.CREDENTIAL_TABLE_DB_STRING;
        }else{
            query = "SELECT * FROM " + table ;
        }
        ResultSet ret = statement.executeQuery(query);
        return ret;
    }
    
    public ResultSet select(String query) throws SQLException{
        return statement.executeQuery(query);
    }
    
    public boolean isUserNameExist(String username) throws SQLException{
        String query = "SELECT `" + DBConstant.CREDENTIAL_USERNAME + "` FROM " +DBConstant.DATABASE+ "." + DBConstant.CREDENTIAL_TABLE_DB_STRING+ " WHERE " + DBConstant.CREDENTIAL_USERNAME + "='"+username+ "';";
        ResultSet results = statement.executeQuery(query);
        return results.first();
    }

    public ResultSet getPassword(String username , Role r) throws SQLException {
        String query = "SELECT `PASSWORD` FROM `credential` WHERE(USERNAME=\"" + username + "\" AND ROLE=\"" + r.id + "\");";
        return statement.executeQuery(query);
    }
    
    /**
     * @deprecated 
     * @param username
     * @return
     * @throws SQLException 
     */
    public ResultSet getPassword(String username) throws SQLException {
       return getPassword(username, Role.MANAGER);
    }

    private String generateColumnsNames(String[] columnNames) {
        int lenth = columnNames.length;
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < lenth - 1; i++) {
            sb.append("`");
            sb.append(columnNames[i]);
            sb.append("`");
            sb.append(",");
        }
        sb.append("`");
        sb.append(columnNames[lenth - 1]);
        sb.append("`");
        return sb.toString();
    }

    private String generateValuesWithDate(String[] values) {
        int lenth = values.length;
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < lenth ; i++) {
            sb.append("'");
            sb.append(values[i]);
            sb.append("'");
            sb.append(",");
        }
//        sb.append("'");
        sb.append("CURRENT_DATE()");
//        sb.append("'");
        return sb.toString();
    }
    
     private String generateValuesWithOutDate(String[] values) {
        int lenth = values.length;
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < lenth-1 ; i++) {
            sb.append("'");
            sb.append(values[i]);
            sb.append("'");
            sb.append(",");
        }
                sb.append("'");
        sb.append(values[lenth-1]);
                sb.append("'");

        return sb.toString();
    }
}
